package sms.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import sms.entity.Score;
import sms.service.ScoreService;
import sms.vo.UnqualifiedStudent;
import sms.vo.ScoreReport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ScoreServiceImpl implements ScoreService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void addScore(Score score) {
        String sql="insert into score(student_id,class_id,course_id,score,score_status) values(?,?,?,?,?)";
        jdbcTemplate.update(sql,score.getStudentId(),score.getClassId(),score.getCourseId(),score.getScore(),score.getScoreStatus());
    }

    @Override
    public void editScore(Score score) {
        String sql="update score set score=?,score_status=? where id=?";
        jdbcTemplate.update(sql,score.getScore(),score.getScoreStatus(),score.getId());
    }

    @Override
    public List<Score> getScoreList(ScoreQueryType queryType, String queryValue) {
        // 预定义 SQL 查询模板
        Map<ScoreQueryType, String> sqlTemplates = new HashMap<>();
        sqlTemplates.put(ScoreQueryType.BY_STUDENT, "select * from score where student_id = ?");
        sqlTemplates.put(ScoreQueryType.BY_CLASS, "select * from score where class_id = ?");
        sqlTemplates.put(ScoreQueryType.BY_COURSE, "select * from score where course_id = ?");

        // 检查查询类型是否合法
        if (!sqlTemplates.containsKey(queryType)) {
            throw new IllegalArgumentException("Unsupported query type: " + queryType);
        }

        String sql = sqlTemplates.get(queryType);

        // 处理不同类型的查询参数
        Object[] params;
        if (queryType == ScoreQueryType.BY_STUDENT) {
            // 学生ID查询需要转换为整数
            try {
                params = new Object[]{Integer.parseInt(queryValue)};
            } catch (NumberFormatException e) {
                throw new IllegalArgumentException("Invalid student ID: " + queryValue, e);
            }
        } else {
            // 班级和课程查询直接使用字符串参数
            params = new Object[]{queryValue};
        }

        // 执行查询
        return jdbcTemplate.query(sql, params, (rs, rowNum) -> {
            Score score = new Score();
            score.setId(rs.getInt("id"));
            score.setStudentId(rs.getInt("student_id"));
            score.setClassId(rs.getString("class_id"));
            score.setCourseId(rs.getString("course_id"));
            score.setScore(rs.getBigDecimal("score"));
            score.setScoreStatus(rs.getString("score_status"));
            return score;
        });
    }

    // 定义查询类型的枚举
    public enum ScoreQueryType {
        BY_STUDENT,
        BY_CLASS,
        BY_COURSE
    }

    @Override
    public List<ScoreReport> getClassScoreReport() {
        String sql = "SELECT " +
                "sc.class_id AS classId, " +
                "co.course_id AS courseId, " +
                "co.course_name AS courseName, " +
                "COUNT(sc.student_id) AS totalStudents, " +
                "COUNT(CASE WHEN sc.score < 60 THEN sc.student_id END) AS failedStudents, " +
                "COALESCE(AVG(sc.score), 0) AS averageScore " +
                "FROM score sc " +
                "JOIN "course" co ON sc.course_id = co.course_id " +
                "GROUP BY sc.class_id, sc.course_id, co.course_name " +
                "ORDER BY co.course_name";

        return jdbcTemplate.query(sql, new ScoreReportRowMapper());
    }

    @Override
    public List<UnqualifiedStudent> getUnqualifiedStudentsByCourse() {
        String sql = "SELECT " +
                "co.course_id AS courseId, " +
                "co.course_name AS courseName, " +
                "u.id AS studentId, " +
                "u.username AS studentName, " +
                "sc.score AS score " +
                "FROM score sc " +
                "JOIN `user` u ON sc.student_id = u.id " +
                "JOIN course co ON sc.course_id = co.course_id " +
                "WHERE sc.score < 60 " +
                "ORDER BY co.course_id, u.username";

        return jdbcTemplate.query(sql, new UnqualifiedStudentRowMapper());
    }

    // 行映射器，将查询结果映射到ScoreReport对象
    private static class ScoreReportRowMapper implements RowMapper<ScoreReport> {
        @Override
        public ScoreReport mapRow(ResultSet rs, int rowNum) throws SQLException {
            ScoreReport report = new ScoreReport();
            report.setClassId(rs.getString("classId"));
            report.setCourseId(rs.getString("courseId"));
            report.setCourseName(rs.getString("courseName"));
            report.setTotalStudents(rs.getInt("totalStudents"));
            report.setFailedStudents(rs.getInt("failedStudents"));
            report.setAverageScore(rs.getDouble("averageScore"));
            return report;
        }
    }

    // 行映射器，将查询结果映射到UnqualifiedStudent对象
    private static class UnqualifiedStudentRowMapper implements RowMapper<UnqualifiedStudent> {
        @Override
        public UnqualifiedStudent mapRow(ResultSet rs, int rowNum) throws SQLException {
            UnqualifiedStudent student = new UnqualifiedStudent();
            student.setCourseId(rs.getString("courseId"));
            student.setCourseName(rs.getString("courseName"));
            student.setStudentId(rs.getLong("studentId"));
            student.setStudentName(rs.getString("studentName"));
            student.setScore(rs.getDouble("score"));
            return student;
        }
    }
}